package com.example.homework;

import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

@Component
public class BuildDatabase implements ApplicationRunner {
    private final String userName = "sa";
    private final String userPwd = "bjdjr0544";
    private Connection con;
    private Statement st;
    private ResultSet rs;

    @Override
    public void run(ApplicationArguments applicationArguments) {
        buildDatabase();
        buildTable();
    }

    public void buildDatabase() {
        try {
            String dbUrl = "jdbc:sqlserver://localhost:1433;trustServerCertificate=true";
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            String sql = "select name from sys.databases where name = 'homework'";
            rs = st.executeQuery(sql);
            int flag = 0;
            while (rs.next()) {
                if (rs.getString("name").equals("homework")) {
                    flag = 1;
                }
            }
            if (flag == 0) {
                sql = "create database homework";
                st.executeUpdate(sql);
            }
            rs.close();
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
    }

    public void buildTable() {
        try {
            String tbUrl = "jdbc:sqlserver://localhost:1433;database=homework;" +
                    "trustServerCertificate=true";
            con = DriverManager.getConnection(tbUrl, userName, userPwd);
            st = con.createStatement();
            String sql = "select name from sys.tables";
            rs = st.executeQuery(sql);
            int flag0 = 0;
            int flag1 = 0;
            int flag2 = 0;
            int flag3 = 0;
            int flag4 = 0;
            int flag5 = 0;
            int flag6 = 0;
            int flag7 = 0;
            while (rs.next()) {
                if (rs.getString("name").equals("Id_max")) {
                    flag0 = 1;
                } else if (rs.getString("name").equals("Buser")) {
                    flag1 = 1;
                } else if (rs.getString("name").equals("Cuser")) {
                    flag2 = 1;
                } else if (rs.getString("name").equals("Food")) {
                    flag3 = 1;
                } else if (rs.getString("name").equals("Order_shop")) {
                    flag4 = 1;
                } else if (rs.getString("name").equals("Order_food")) {
                    flag5 = 1;
                } else if (rs.getString("name").equals("Cart")) {
                    flag6 = 1;
                } else if (rs.getString("name").equals("Record")) {
                    flag7 = 1;
                }
            }
            if (flag0 == 0) {
                sql = "create table Id_max(" +
                        "temp int not null unique, " +
                        "Omax int not null," +
                        "Umax int not null," +
                        "Fmax int not null," +
                        "Pwd nvarchar(50) not null," +
                        "constraint primary_Id_max_temp primary key (temp))";
                st.executeUpdate(sql);
                sql = "insert into Id_max values (0, 0, 0, 0, 'bjdjr0544')";
                st.executeUpdate(sql);
            }
            if (flag1 == 0) {
                sql = "create table Buser(" +
                        "Bid int not null unique, " +
                        "Btele nvarchar(20) not null unique," +
                        "Bname nvarchar(50) not null unique," +
                        "Bsex int," +
                        "Bpwd nvarchar(50) not null," +
                        "Bimg varbinary," +
                        "Baddr nvarchar(50)," +
                        "Bstatus nvarchar(20) not null," +
                        "Bdel int not null," +
                        "constraint primary_Buser_Bid primary key (Bid)," +
                        "constraint check_Buser_Bsex check (Bsex = 0 or Bsex = 1)," +
                        "constraint check_Buser_Bstatus check (Bstatus = N'未审核' " +
                        "or Bstatus = N'已审核')," +
                        "constraint check_Buser_Bdel check (Bdel = 1 or Bdel = 0))";
                st.executeUpdate(sql);
            }
            if (flag2 == 0) {
                sql = "create table Cuser(" +
                        "Cid int not null unique, " +
                        "Ctele nvarchar(20) not null unique," +
                        "Cname nvarchar(50) not null unique," +
                        "Csex int," +
                        "Cpwd nvarchar(50) not null," +
                        "Cimg varbinary," +
                        "Caddr nvarchar(50)," +
                        "Cstatus nvarchar(20) not null," +
                        "Cdel int not null," +
                        "constraint primary_Cuser_Cid primary key (Cid)," +
                        "constraint check_Cuser_Csex check (Csex = 0 or Csex = 1)," +
                        "constraint check_Cuser_Cstatus check (Cstatus = N'未审核' or " +
                        "Cstatus = N'已审核')," +
                        "constraint check_Cuser_Cdel check (Cdel = 1 or Cdel = 0))";
                st.executeUpdate(sql);
            }
            if (flag3 == 0) {
                sql = "create table Food(" +
                        "Fid int not null unique," +
                        "Bid int not null," +
                        "Fname nvarchar(50) not null," +
                        "Fprice float not null," +
                        "Fimg varbinary," +
                        "Fdesc nvarchar(100)," +
                        "Fstock int not null," +
                        "Ftype nvarchar(20) not null," +
                        "Frec int not null," +
                        "Fstatus nvarchar(20) not null," +
                        "Fpass nvarchar(20) not null," +
                        "Fdel int not null," +
                        "constraint primary_Food_Fid primary key (Fid)," +
                        "constraint foreign_Food_Bid foreign key (Bid) references Buser(Bid)," +
                        "constraint check_Food_Fstock check (Fstock >= 0 and Fstock <= 100)," +
                        "constraint check_Food_Frec check (Frec >= 0 and Frec <= 5)," +
                        "constraint check_Food_Fstatus check (Fstatus = N'下架' or " +
                        "Fstatus = N'上架')," +
                        "constraint check_Food_Fpass check (Fpass = N'未审核' or Fpass = N'已审核')," +
                        "constraint check_Food_Fdel check (Fdel = 1 or Fdel = 0))";
                st.executeUpdate(sql);
            }
            if (flag4 == 0) {
                sql = "create table Order_shop(" +
                        "Oid int not null unique," +
                        "Otime datetime not null," +
                        "Ctele nvarchar(20) not null," +
                        "Bid int not null," +
                        "Cid int not null," +
                        "Oval float not null," +
                        "Ostatus nvarchar(20) not null," +
                        "Otip nvarchar(100) not null," +
                        "Odel int not null," +
                        "constraint primary_Oshop_Oid primary key (Oid)," +
                        "constraint foreign_Oshop_Bid foreign key (Bid) references Buser(Bid)," +
                        "constraint foreign_Oshop_Cid foreign key (Cid) references Cuser(Cid)," +
                        "constraint check_Oshop_Ostatus check (Ostatus = N'未通过' or " +
                        "Ostatus = N'未接单' or " + "Ostatus = N'已接单' or Ostatus = N'已完成')," +
                        "constraint check_Oshop_Odel check (Odel = 1 or Odel = 0))";
                st.executeUpdate(sql);
            }
            if (flag5 == 0) {
                sql = "create table Order_food(" +
                        "Oid int not null," +
                        "Fid int not null," +
                        "Fname nvarchar(50) not null," +
                        "Fimg varbinary," +
                        "Fnum int not null," +
                        "Fvalue float not null," +
                        "constraint primary_Ofood_Oid_Fid primary key (Oid, Fid)," +
                        "constraint foreign_Ofood_Oid foreign key (Oid) " +
                        "references Order_shop(Oid)," +
                        "constraint foreign_Ofood_Fid foreign key (Fid) references Food(Fid))";
                st.executeUpdate(sql);
            }
            if (flag6 == 0) {
                sql = "create table Cart(" +
                        "Bid int not null," +
                        "Cid int not null," +
                        "Fid int not null ," +
                        // "Fname nvarchar(50) not null," +
                        // "Fimg varbinary," +
                        "Fnum int not null," +
                        // "Fvalue float not null," +
                        "constraint primary_Cart_Bid_Cid_Fid primary key (Bid, Cid, Fid)," +
                        "constraint foreign_Cart_Bid foreign key (Bid) references Buser(Bid)," +
                        "constraint foreign_Cart_Cid foreign key (Cid) references Cuser(Cid)," +
                        "constraint foreign_Cart_Fid foreign key (Fid) references Food(Fid))";
                st.executeUpdate(sql);
            }
            if (flag7 == 0) {
                sql = "create table Record(" +
                        "Rtype int not null," +
                        "Rtime datetime not null," +
                        "Rid int not null," +
                        "Ukind int," +
                        "Utele nvarchar(20)," +
                        "Uname nvarchar(50)," +
                        "Usex int," +
                        "Upwd nvarchar(50)," +
                        "Uimg varbinary," +
                        "Uaddr nvarchar(50)," +
                        "Fid int," +
                        "Fname nvarchar(50)," +
                        "Fprice float," +
                        "Fimg varbinary," +
                        "Fdesc nvarchar(100)," +
                        "Fstock int," +
                        "Ftype nvarchar(20)," +
                        "Frec int," +
                        "Fstatus nvarchar(20)," +
                        "Oid int," +
                        "Ostatus nvarchar(20)," +
                        "Bid int," +
                        "Otip nvarchar(100)" +
                        "constraint foreign_Record_Bid foreign key (Bid) references Buser(Bid)," +
                        "constraint foreign_Record_Oid foreign key (Oid) " +
                        "references Order_shop(Oid)," +
                        "constraint foreign_Record_Fid foreign key (Fid) references Food(Fid), " +
                        "constraint check_Record_Rtype check (Rtype >= 1 and Rtype <= 10)," +
                        "constraint check_Record_Usex check (Usex = 0 or Usex = 1)," +
                        "constraint check_Record_Fstock check (Fstock >=0 and Fstock <= 100)," +
                        "constraint check_Record_Frec check (Frec >= 0 and Frec <= 5)," +
                        "constraint check_Record_Fstatus check (Fstatus = N'上架' or " +
                        "Fstatus = N'下架')," +
                        "constraint check_Record_Ostatus check (Ostatus = N'未接单' or " +
                        "Ostatus = N'已接单' or Ostatus = N'已完成'))";
                st.executeUpdate(sql);
            }
            rs.close();
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
    }
}
